﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using AdinZ.DB;

/**
* @Author: AdinZ
* @Q  Q : 1454269
* @Email:adinz@adinz.com
* @Created: 2014/3/31 16:40:34
*
* */
namespace Novel.Sys.Dal
{
    public class novel_user
    {
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Model.novel_user model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO [novel_user] ([gid], [nick], [name], [passwd], [email], [phone], [avatar],[sex]) VALUES (");
            strSql.Append("@gid,@nick,@name,@passwd,@email,@phone,@avatar,@sex");
            strSql.Append(");select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@gid", SqlDbType.Int),
                    new SqlParameter("@nick", SqlDbType.NVarChar,20),
                    new SqlParameter("@name", SqlDbType.NVarChar,20),
                    new SqlParameter("@passwd", SqlDbType.NVarChar,36),
                    new SqlParameter("@email", SqlDbType.NVarChar,50),
                    new SqlParameter("@phone", SqlDbType.NVarChar,20),
                    new SqlParameter("@avatar", SqlDbType.NVarChar,100),
                    new SqlParameter("@sex", SqlDbType.Int)};
            parameters[0].Value = model.Gid;
            parameters[1].Value = model.Nick;
            parameters[2].Value = model.Name;
            parameters[3].Value = model.Passwd;
            parameters[4].Value = model.Email;
            parameters[5].Value = model.Phone;
            parameters[6].Value = model.Avatar;
            parameters[7].Value = model.Sex;
           object obj= DbHelpers.GetSingle(strSql.ToString(), parameters);
           if (obj == null)
           {
               return 0;
           }
           else
           {
               return Convert.ToInt32(obj);
           }
        }
        /// <summary>
        /// 验证用户名和密码
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="Passwd"></param>
        /// <returns></returns>
        public bool Exists(string Name, string Passwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [novel_user]");
            strSql.Append(" where name=@name and passwd=@passwd");
            SqlParameter[] parameters = {
                    new SqlParameter("@name", SqlDbType.NVarChar,20),
                    new SqlParameter("@passwd", SqlDbType.NVarChar,36)};
            parameters[0].Value = Name;
            parameters[1].Value = Passwd;
            return DbHelpers.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 验证用户名
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public bool Exists(string Name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [novel_user]");
            strSql.Append(" where name=@name");
            SqlParameter[] parameters = {
                    new SqlParameter("@name", SqlDbType.NVarChar,20)};
            parameters[0].Value = Name;
            return DbHelpers.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public Model.novel_user GetModel(string Name)
        {
            Model.novel_user model = new Model.novel_user();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [novel_user] where name=@name");
            SqlParameter[] parameters = {
                    new SqlParameter("@name", SqlDbType.NVarChar,20)};
            parameters[0].Value = Name;
            DataSet ds = DbHelpers.GetDataSet(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["gid"] != null && ds.Tables[0].Rows[0]["gid"].ToString() != "")
                {
                    model.Gid = int.Parse(ds.Tables[0].Rows[0]["gid"].ToString());
                }
                if (ds.Tables[0].Rows[0]["name"] != null && ds.Tables[0].Rows[0]["name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["nick"] != null && ds.Tables[0].Rows[0]["nick"].ToString() != "")
                {
                    model.Nick = ds.Tables[0].Rows[0]["nick"].ToString();
                }
                if (ds.Tables[0].Rows[0]["passwd"] != null && ds.Tables[0].Rows[0]["passwd"].ToString() != "")
                {
                    model.Passwd = ds.Tables[0].Rows[0]["passwd"].ToString();
                }
                if (ds.Tables[0].Rows[0]["email"] != null && ds.Tables[0].Rows[0]["email"].ToString() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["email"].ToString();
                }
                if (ds.Tables[0].Rows[0]["phone"] != null && ds.Tables[0].Rows[0]["phone"].ToString() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["phone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["avatar"] != null && ds.Tables[0].Rows[0]["avatar"].ToString() != "")
                {
                    model.Avatar = ds.Tables[0].Rows[0]["avatar"].ToString();
                }
                if (ds.Tables[0].Rows[0]["sex"] != null && ds.Tables[0].Rows[0]["sex"].ToString() != "")
                {
                    model.Sex = int.Parse(ds.Tables[0].Rows[0]["sex"].ToString());
                }
                return model;
            }
            else
                return null;
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public Model.novel_user GetModel(int ID)
        {
            Model.novel_user model = new Model.novel_user();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [novel_user] where ID=@ID");
            SqlParameter[] parameters = {
                    new SqlParameter("@ID", SqlDbType.Int)};
            parameters[0].Value = ID;
            DataSet ds = DbHelpers.GetDataSet(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["gid"] != null && ds.Tables[0].Rows[0]["gid"].ToString() != "")
                {
                    model.Gid = int.Parse(ds.Tables[0].Rows[0]["gid"].ToString());
                }
                if (ds.Tables[0].Rows[0]["name"] != null && ds.Tables[0].Rows[0]["name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["nick"] != null && ds.Tables[0].Rows[0]["nick"].ToString() != "")
                {
                    model.Nick = ds.Tables[0].Rows[0]["nick"].ToString();
                }
                if (ds.Tables[0].Rows[0]["passwd"] != null && ds.Tables[0].Rows[0]["passwd"].ToString() != "")
                {
                    model.Passwd = ds.Tables[0].Rows[0]["passwd"].ToString();
                }
                if (ds.Tables[0].Rows[0]["email"] != null && ds.Tables[0].Rows[0]["email"].ToString() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["email"].ToString();
                }
                if (ds.Tables[0].Rows[0]["phone"] != null && ds.Tables[0].Rows[0]["phone"].ToString() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["phone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["avatar"] != null && ds.Tables[0].Rows[0]["avatar"].ToString() != "")
                {
                    model.Avatar = ds.Tables[0].Rows[0]["avatar"].ToString();
                }
                if (ds.Tables[0].Rows[0]["sex"] != null && ds.Tables[0].Rows[0]["sex"].ToString() != "")
                {
                    model.Sex = int.Parse(ds.Tables[0].Rows[0]["sex"].ToString());
                }
                return model;
            }
            else
                return null;
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM novel_user ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelpers.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strWhere = " Where " + strWhere;
            }

            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                orderby = " Order By " + orderby;
            }

            //startIndex = startIndex - 1;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM novel_user Where ID Not IN ");
            strSql.Append("(Select Top " + startIndex + " ID From novel_user" + strWhere + orderby + ")");
            strSql.Append(" And ID In ");
            strSql.Append("(Select Top " + endIndex + " ID From novel_user" + strWhere + orderby + ")");
            strSql.Append(orderby);
            return DbHelpers.GetDataSet(strSql.ToString());
        }

    }

}

